SET QUOTED_IDENTIFIER OFF;
SET ANSI_NULLS ON;
SET NOCOUNT ON;
GO

DECLARE @AlteredFlag BIT
SELECT @AlteredFlag	= 0;

IF EXISTS (	SELECT *
			FROM INFORMATION_SCHEMA.TABLES
			WHERE TABLE_SCHEMA = 'dbo'
				AND TABLE_NAME = 'Product'
				AND TABLE_TYPE = 'BASE TABLE' )
	-------------------------------------------------------------------------------
	--Table Already Exists - Execute ALTER Statements
	-------------------------------------------------------------------------------
	BEGIN
		
		IF NOT EXISTS ( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Product' AND COLUMN_NAME = 'UomID' )
		BEGIN
			ALTER TABLE dbo.Product ADD UomID INT NULL;			
			PRINT 'dbo.Product.UomID column added.';
			SET @AlteredFlag = 1;
			ALTER TABLE [dbo].[Product]  WITH CHECK ADD  CONSTRAINT [FK_Product_UOM] FOREIGN KEY([UomID])
			REFERENCES [dbo].[UOM] ([ID]);
			PRINT 'Added Constraint FK_Product_UOM successfully'
			
		END
		
		IF ( @AlteredFlag = 1 )
			BEGIN
				PRINT 'Table Product altered with latest changes.';		
				PRINT 'Table Product successfully upgraded.';
			END
		ELSE
			BEGIN
				PRINT 'Table Product requires no changes.';
			END
		END
ELSE
	-------------------------------------------------------------------------------
	--Table Does Not Exist - Create the Table
	-------------------------------------------------------------------------------
	BEGIN
		CREATE TABLE [dbo].[Product](
			[ID] [int] IDENTITY(1,1) NOT NULL,
			[Code] [nvarchar](50) NULL,
			[Name] [nvarchar](250) NOT NULL,
			[Description] [nvarchar](3000) NULL,
			[CategoryID] [int] NOT NULL,
			[CompanyID] [int] NOT NULL,
			[ManufacturerID] [int] NOT NULL,
			[UPC] [nvarchar](50) NULL,
			[Model] [nvarchar](100) NULL,
			[DynamicProperties] [xml] NULL,
			[IsActive] [bit] NOT NULL,
			[IsDeleted] [bit] NOT NULL,
			[Price] [money] NOT NULL,
			[UomID] INT NULL,
			[ProductUrl] [nvarchar](250) NOT NULL,
			[Created] [datetime] NOT NULL,
			[Modified] [datetime] NOT NULL,
		CONSTRAINT [PK_Product] PRIMARY KEY NONCLUSTERED 
		(
			[ID] ASC
		)
		WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 100) ON [PRIMARY]
		) ON [PRIMARY];

		--GO

		ALTER TABLE [dbo].[Product]  WITH CHECK ADD  CONSTRAINT [FK_Product_Category] FOREIGN KEY([CategoryID])
		REFERENCES [dbo].[Category] ([ID])
		--GO

		ALTER TABLE [dbo].[Product] CHECK CONSTRAINT [FK_Product_Category]
		--GO

		ALTER TABLE [dbo].[Product]  WITH CHECK ADD  CONSTRAINT [FK_Product_Company] FOREIGN KEY([CompanyID])
		REFERENCES [dbo].[Company] ([ID])
		--GO

		ALTER TABLE [dbo].[Product] CHECK CONSTRAINT [FK_Product_Company]
		--GO

		ALTER TABLE [dbo].[Product]  WITH CHECK ADD  CONSTRAINT [FK_Product_Manufacturer] FOREIGN KEY([ManufacturerID])
		REFERENCES [dbo].[Manufacturer] ([ID])
		--GO
		
		ALTER TABLE [dbo].[Product]  WITH CHECK ADD  CONSTRAINT [FK_Product_UOM] FOREIGN KEY([UomID])
		REFERENCES [dbo].[UOM] ([ID])

		ALTER TABLE [dbo].[Product] CHECK CONSTRAINT [FK_Product_Manufacturer]
		--GO

		ALTER TABLE [dbo].[Product] ADD  CONSTRAINT [DF_Product_IsActive]  DEFAULT ((1)) FOR [IsActive]
		--GO

		ALTER TABLE [dbo].[Product] ADD  CONSTRAINT [DF_Product_IsDeleted]  DEFAULT ((0)) FOR [IsDeleted]
		--GO

		ALTER TABLE [dbo].[Product] ADD  CONSTRAINT [DF_Product_Price]  DEFAULT ((0)) FOR [Price]
		--GO

		ALTER TABLE [dbo].[Product] ADD  DEFAULT (getdate()) FOR [Created]
		--GO

		ALTER TABLE [dbo].[Product] ADD  DEFAULT (getdate()) FOR [Modified]		
		--GO
	-------------------------------------------------------------------------------
	--	END OF CREATE STATEMENTS.
	--	METADATA AND SUCCESS/ERROR MESSAGES BELOW.
	-------------------------------------------------------------------------------
	-- Make sure object was created successfully.
	IF EXISTS (	SELECT *
				FROM INFORMATION_SCHEMA.TABLES
				WHERE TABLE_SCHEMA = 'dbo'
					AND TABLE_NAME = 'Product'
					AND TABLE_TYPE = 'BASE TABLE' )


		BEGIN
			PRINT 'Table Product created successfully.';	
		END
	ELSE
		BEGIN
			PRINT 'ERROR: Failed to create Table Product.';
		END
	END
GO



